package m_mysql


import (  
    "database/sql"  
    "fmt"  
    _ "github.com/go-sql-driver/mysql" //导入mysql包  
    "time"  
    "strings"
    "encoding/json"
    "strconv"
)


type Mydb struct {
	Db *sql.DB
} 

type M_code struct {
	Code 		int
	Mbool 		bool
	Msg 		string 
	Data 		Doc_1
}

type Doc_1 struct {
	Id 			int  	
	Account 	string 	
	Password 	string 	
	Logo 		string  
	Channel 	string  
	Model 		string  
	Game_id 	int 
	Time   		string	
}

type Doc_2 struct {
	Id  		int
	Name    	string
	Time 		string
}


func Open_db(database string) Mydb {
	var mdb  Mydb
	db, _:= sql.Open("mysql", database)
    mdb.Db = db
    mdb.Db.SetConnMaxLifetime(time.Minute * 3)
	mdb.Db.SetMaxOpenConns(10)
	mdb.Db.SetMaxIdleConns(10)
	return mdb
}


func sql_see(t bool, db *Mydb, sql string, game string) *Doc_2 {
	begin, _ := db.Db.Begin()
	retdata  := Doc_2{}
	rows     := begin.QueryRow(sql,game)
    rows.Scan(&retdata.Id, &retdata.Name, &retdata.Time)
    if (retdata.Id == 0 && t) {
    	sq1 	 := "insert into my_game(name, time) values (?, ?)"			
    	datetime := time.Now().String()
    	datav 	 := strings.Split(datetime, " ")
    	//fmt.Println(datav[0])
    	_, err := begin.Exec(sq1, game, datav[0])
		if err != nil{
			begin.Rollback()
			time.Sleep(3 * time.Second)
			return sql_see(t, db, sql, game)
		}
		err = begin.Commit()
		if err != nil {
			retdata.Name = err.Error()
			return &retdata
		}
		return sql_see(t, db, sql, game)
    }
    return &retdata
}

//创 201
func (db *Mydb) InIt_Data() *M_code {
	ret := &M_code{}
	begin, err := db.Db.Begin()
	begin.Exec("DROP TABLE IF EXISTS my_game")
	begin.Exec("DROP TABLE IF EXISTS my_account")
	begin.Exec("DROP TABLE IF EXISTS my_complete")
	begin.Exec("DROP TABLE IF EXISTS child_account")

	sq2 := `create table my_account (
		id 			INT 	 	NOT NULL PRIMARY KEY AUTO_INCREMENT,
		account 	CHAR(30) 	NOT NULL,
		password 	CHAR(30) 	NOT NULL,
		logo 		CHAR(4)  	NOT NULL,
		channel		CHAR(10)	NOT NULL,
		model		CHAR(10)	NOT NULL,
		game_id 	INT 	 	NOT NULL,
		FOREIGN KEY (game_id) REFERENCES my_game (id)
		)ENGINE=innodb DEFAULT CHARSET=utf8;`

	sq1 := `create table my_game (
		id 			INT 	 	NOT NULL PRIMARY KEY AUTO_INCREMENT,
		name 		CHAR(30) 	NOT NULL,
		time 		CHAR(30)	NOT NULL
		)ENGINE=innodb DEFAULT CHARSET=utf8;`

	sq3 := `create table my_complete (
		id 			INT 	 	NOT NULL PRIMARY KEY AUTO_INCREMENT,
		game 		CHAR(30) 	NOT NULL,
		time 		CHAR(30)	NOT NULL,
		channel		CHAR(30)	NOT NULL
		)ENGINE=innodb DEFAULT CHARSET=utf8;`

	sq4 := `create table child_account (
		id 			INT 		NOT NULL PRIMARY KEY AUTO_INCREMENT,
		ip 			CHAR(30)	NOT NULL,
		child  		CHAR(30)	NOT NULL,
		user		CHAR(30)	NOT NULL,
		password  	CHAR(30) 	NOT NULL
		)ENGINE=innodb DEFAULT CHARSET=utf8;`

	for k,v := range []string{sq1,sq2,sq3,sq4} {
		_, err = begin.Exec(v)
		if err != nil {
			begin.Rollback()
			ret.Code  = 201
			ret.Mbool = false
			ret.Msg   = err.Error()
			return ret
		}
		fmt.Println(k)
	}
	err = begin.Commit()
	if err != nil {
		ret.Code  = 201
		ret.Mbool = false
		ret.Msg   = err.Error()
		return ret
	}
	ret.Code  = 200
	ret.Mbool = true
	return ret
}


//增 202
func (db *Mydb) Insert_accanjiu(game string, data map[string]map[string]string) *M_code {
	ret := &M_code{}
	begin, err := db.Db.Begin()
	sql := "SELECT * FROM my_game WHERE name=?"
    mdata := sql_see(true, db, sql, game)
    if mdata.Id == 0 {
    	ret.Mbool = false
    	ret.Code  = 202
    	ret.Msg   = mdata.Name
    	return ret
    }
    //sql = "INSERT INTO my_account AS b1(account, password, logo, channel, model, game_id) INNER JOIN (SELECT id FROM my_game WHERE name=?) AS b2 ON b1.game_id=b2.id VALUES (?, ?, ?, ?, ?, b2.id)"
	sql = "INSERT INTO my_account(account, password, logo, channel, model, game_id) VALUES (?, ?, ?, ?, ?, ?)"
	for _,v := range data {
		_, err = begin.Exec(sql, v["account"], v["password"], v["logo"], v["channel"], v["model"], mdata.Id)
		if err != nil{
			begin.Rollback()
    		ret.Mbool = false
    		ret.Code  = 202
    		ret.Msg   = err.Error()
    		return ret
		}
	}
	err = begin.Commit()
    if err != nil {
    	ret.Mbool = false
    	ret.Code  = 202
    	ret.Msg   = err.Error()
    	return ret
    }
    ret.Mbool = true
    ret.Code  = 200
    return ret
}

//改 203
func (db *Mydb) Update_accanjiu(new_data map[string]string, old_data map[string]string, sum int) *M_code {
	ret := &M_code{}
	sql := "SELECT * FROM my_game WHERE name=?"
	ydata := sql_see(false, db, sql, old_data["game"])
    mdata := sql_see(true, db, sql, new_data["game"])
    if (mdata.Id == 0 || ydata.Id == 0) {
    	ret.Mbool = false
    	ret.Code  = 203
    	ret.Msg   = mdata.Name
    	return ret
    }
    begin, err := db.Db.Begin()
    //sql = "UPDATE my_account AS b2 INNER JOIN my_game AS b1 ON b1.id = b2.game_id SET b2.game_id=?,b2.channel=?,b2.model=? WHERE b1.name=? AND b2.channel=? AND b2.model=? AND logo='0'"
    //"UPDATE my_account AS b2 INNER JOIN my_game AS b1 ON b1.id = b2.game_id inner join(select id from my_game where name='{}') as t SET b2.game_id=t.id,b2.channel='{}',b2.model='{}' WHERE b1.name='{}' AND b2.channel='{}' AND b2.model='{}' AND logo='0'
    sql = "UPDATE my_account SET game_id=?,channel=?,model=? WHERE game_id=? AND channel=? AND model=? AND logo='0' limit ?"
    //fmt.Println(sql,mdata.Id,new_data["channel"],new_data["model"],ydata.Id,old_data["channel"],old_data["model"],sum)
	_, err = begin.Exec(sql,mdata.Id,new_data["channel"],new_data["model"],ydata.Id,old_data["channel"],old_data["model"],sum)
	if err != nil{
		begin.Rollback()
    	ret.Mbool = false
    	ret.Code  = 203
    	ret.Msg   = err.Error()
    	return ret
	}
	err = begin.Commit()
    if err != nil {
    	ret.Mbool = false
    	ret.Code  = 203
    	ret.Msg   = err.Error()
    	return ret
    }
    ret.Mbool = true
    ret.Code  = 200
    return ret

}


//查 206
func (db *Mydb) Select_accanjiu(game string, Channel string, Model string) *M_code {
	ret := &M_code{}
	begin, err := db.Db.Begin()
	sql := "SELECT b1.*,b2.* FROM my_game AS b1 INNER JOIN my_account AS b2 ON b1.id = b2.game_id WHERE b1.name=? AND b2.channel=? AND b2.model=?"
	rows, err := begin.Query(sql,game,Channel,Model)
    if err != nil {
    	ret.Mbool = false
    	ret.Code  = 206
    	ret.Msg   = err.Error()
    	return ret
    }
    var d = 0
    for rows.Next() {  
    	d += 1
	}  
	ret.Mbool = true
    ret.Code  = 200
    ret.Msg   = strconv.Itoa(d)
	return ret
}


//删 204
func (db *Mydb) M_del(id int) *M_code {
	ret := &M_code{}
	begin, err := db.Db.Begin()
	sql := "DELETE FROM my_account WHERE id=?"
	_, err = begin.Exec(sql,id)
	if err != nil{
		begin.Rollback() 
    	ret.Mbool = false
    	ret.Code  = 204
    	ret.Msg   = err.Error()
    	return ret
	}
	err = begin.Commit()
    if err != nil {
    	ret.Mbool = false
    	ret.Code  = 204
    	ret.Msg   = err.Error()
    	return ret
    }
    ret.Mbool = true
    ret.Code  = 200
    return ret
}

//取 205
func (db *Mydb) M_get(game string, Channel string, Model string) *M_code {
	ret := &M_code{}
	begin, err := db.Db.Begin()
	sql := "SELECT b2.*,b1.time FROM my_account AS b2 INNER JOIN my_game AS b1 ON b1.id = b2.game_id WHERE b1.name=? AND b2.channel=? AND b2.model=? AND b2.logo='0' ORDER BY RAND() LIMIT 1"
	rows := begin.QueryRow(sql, game, Channel, Model)
	var doc Doc_1
	rows.Scan(&doc.Id, &doc.Account, &doc.Password, &doc.Logo, &doc.Channel, &doc.Model, &doc.Game_id, &doc.Time)
	sql = "UPDATE my_account SET logo='1' where id=?"
	_, err = begin.Exec(sql,doc.Id)
	if err != nil{
		begin.Rollback() 
    	ret.Mbool = false
    	ret.Code  = 205
    	ret.Msg   = err.Error()
    	return ret
	}
	err = begin.Commit()
	if err != nil{
    	ret.Mbool = false
    	ret.Code  = 205
    	ret.Msg   = err.Error()
    	return ret
	}
	ret.Mbool = true
    ret.Code  = 200
    ret.Data  = doc
    return ret
}


func (db *Mydb) Select_game() []Doc_2 {
	begin, err := db.Db.Begin()
	sql := "SELECT * FROM my_game"
	rows, err := begin.Query(sql)
    if err != nil {
    	ret.Mbool = false
    	ret.Code  = 211
    	ret.Msg   = err.Error()
    	return ret
    }
    var d []Doc_2
    for rows.Next() {  
    	c := Doc_2{}
    	rows.Scan(&c.Id,&c.Name,&c.Time)
    	d = append(d,c)
	}  
	return d
}

//白名单
//子账号